import { BusinessException } from '../../types/exceptions.js';
import { Pool as MySQLPool, ResultSetHeader } from 'mysql2/promise';
import { Pool as PgPool } from 'pg';
import {
  DatabaseOperations,
  DatabaseOperationsFactory,
  BaseMySQLOperations,
  BasePostgreSQLOperations,
} from '../../utils/database-operations.js';
import {
  SysRoleMenu,
  CreateSysRoleMenuDto,
  UpdateSysRoleMenuDto,
  SysRoleMenuQueryDto,
  RoleMenuPermissionDto,
  RoleMenuTreeDto,
} from '../../entities/system/SysRoleMenu.js';

// SysRoleMenu特定的数据库操作接口
interface SysRoleMenuOperations extends DatabaseOperations<SysRoleMenu> {
  getByRoleId(roleId: number): Promise<SysRoleMenu[]>;
  getByMenuId(menuId: number): Promise<SysRoleMenu[]>;
  getByRoleAndMenu(roleId: number, menuId: number): Promise<SysRoleMenu | null>;
  deleteByRoleId(roleId: number): Promise<boolean>;
  batchCreate(roleMenus: CreateSysRoleMenuDto[]): Promise<boolean>;
  getRoleMenuTree(roleId: number): Promise<RoleMenuTreeDto>;
  setRoleMenuPermissions(data: RoleMenuPermissionDto): Promise<boolean>;
}

// MySQL数据库操作实现
class MySQLOperations extends BaseMySQLOperations<SysRoleMenu> implements SysRoleMenuOperations {
  constructor(pool: MySQLPool) {
    super(pool);
  }

  async create(roleMenu: CreateSysRoleMenuDto): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_role_menu (
                      role_id, menu_id, is_half, time, update_time
                  ) VALUES (?, ?, ?, ?, ?)`;
    const params: (string | number)[] = [
      roleMenu.role_id as number,
      roleMenu.menu_id as number,
      roleMenu.is_half as number,
      now,
      now,
    ];

    const [result] = await this.pool.execute(sql, params);
    return (result as ResultSetHeader).insertId;
  }

  async update(id: number, roleMenu: UpdateSysRoleMenuDto): Promise<boolean> {
    const updateFields: string[] = [];
    const params: (string | number)[] = [];

    Object.entries(roleMenu).forEach(([key, value]) => {
      if (value !== undefined && key !== 'id' && key !== 'time') {
        updateFields.push(`${key} = ?`);
        params.push(value as string | number);
      }
    });

    if (updateFields.length === 0) {
      return false;
    }

    updateFields.push('update_time = ?');
    params.push(Date.now());
    params.push(id);

    const sql = `UPDATE sys_role_menu SET ${updateFields.join(', ')} WHERE id = ? AND disabled = 0`;
    const [result] = await this.pool.execute(sql, params);
    return (result as ResultSetHeader).affectedRows > 0;
  }

  async delete(id: number): Promise<boolean> {
    const sql = 'UPDATE sys_role_menu SET disabled = 1, update_time = ? WHERE id = ?';
    const [result] = await this.pool.execute(sql, [Date.now(), id]);
    return (result as ResultSetHeader).affectedRows > 0;
  }

  async getByRoleId(roleId: number): Promise<SysRoleMenu[]> {
    const sql = 'SELECT * FROM sys_role_menu WHERE role_id = ? AND disabled = 0';
    const [rows] = await this.pool.execute(sql, [roleId]);
    return rows as SysRoleMenu[];
  }

  async getByMenuId(menuId: number): Promise<SysRoleMenu[]> {
    const sql = 'SELECT * FROM sys_role_menu WHERE menu_id = ? AND disabled = 0';
    const [rows] = await this.pool.execute(sql, [menuId]);
    return rows as SysRoleMenu[];
  }

  async getByRoleAndMenu(roleId: number, menuId: number): Promise<SysRoleMenu | null> {
    const sql = 'SELECT * FROM sys_role_menu WHERE role_id = ? AND menu_id = ? AND disabled = 0';
    const [rows] = await this.pool.execute(sql, [roleId, menuId]);
    const result = rows as SysRoleMenu[];
    return result.length > 0 ? result[0] : null;
  }

  async deleteByRoleId(roleId: number): Promise<boolean> {
    const sql = 'UPDATE sys_role_menu SET disabled = 1, update_time = ? WHERE role_id = ?';
    const [result] = await this.pool.execute(sql, [Date.now(), roleId]);
    return (result as ResultSetHeader).affectedRows > 0;
  }

  async batchCreate(roleMenus: CreateSysRoleMenuDto[]): Promise<boolean> {
    if (roleMenus.length === 0) return true;

    const now = Date.now();
    const values = roleMenus.map(() => '(?, ?, ?, ?, ?)').join(', ');
    const sql = `INSERT INTO sys_role_menu (role_id, menu_id, is_half, time, update_time) VALUES ${values}`;

    const params: (string | number)[] = [];
    roleMenus.forEach(roleMenu => {
      params.push(
        roleMenu.role_id as number,
        roleMenu.menu_id as number,
        roleMenu.is_half as number,
        now,
        now
      );
    });

    const [result] = await this.pool.execute(sql, params);
    return (result as ResultSetHeader).affectedRows > 0;
  }

  async getRoleMenuTree(roleId: number): Promise<RoleMenuTreeDto> {
    const sql = 'SELECT menu_id, is_half FROM sys_role_menu WHERE role_id = ? AND disabled = 0';
    const [rows] = await this.pool.execute(sql, [roleId]);
    const roleMenus = rows as { menu_id: number; is_half: number }[];

    const checkedMenuIds: number[] = [];
    const halfCheckedMenuIds: number[] = [];

    roleMenus.forEach(rm => {
      if (rm.is_half === 0) {
        checkedMenuIds.push(rm.menu_id);
      } else {
        halfCheckedMenuIds.push(rm.menu_id);
      }
    });

    return {
      role_id: roleId,
      checked_menu_ids: checkedMenuIds,
      half_checked_menu_ids: halfCheckedMenuIds,
    };
  }

  async setRoleMenuPermissions(data: RoleMenuPermissionDto): Promise<boolean> {
    const connection = await this.pool.getConnection();
    try {
      await connection.beginTransaction();

      // 删除该角色的所有菜单权限
      await connection.execute(
        'UPDATE sys_role_menu SET disabled = 1, update_time = ? WHERE role_id = ?',
        [Date.now(), data.role_id]
      );

      // 批量插入新的菜单权限
      const roleMenus: CreateSysRoleMenuDto[] = [];

      // 添加选中的菜单
      data.menu_ids.forEach(menuId => {
        roleMenus.push({
          role_id: data.role_id,
          menu_id: menuId,
          is_half: 0,
          time: Date.now(),
          update_time: Date.now(),
          disabled: 0,
        });
      });

      // 添加半选的菜单
      if (data.half_menu_ids && data.half_menu_ids.length > 0) {
        data.half_menu_ids.forEach(menuId => {
          roleMenus.push({
            role_id: data.role_id,
            menu_id: menuId,
            is_half: 1,
            time: Date.now(),
            update_time: Date.now(),
            disabled: 0,
          });
        });
      }

      if (roleMenus.length > 0) {
        const now = Date.now();
        const values = roleMenus.map(() => '(?, ?, ?, ?, ?)').join(', ');
        const sql = `INSERT INTO sys_role_menu (role_id, menu_id, is_half, time, update_time) VALUES ${values}`;

        const params: (string | number)[] = [];
        roleMenus.forEach(roleMenu => {
          params.push(
            roleMenu.role_id as number,
            roleMenu.menu_id as number,
            roleMenu.is_half as number,
            now,
            now
          );
        });

        await connection.execute(sql, params);
      }

      await connection.commit();
      return true;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  async getById(id: number): Promise<SysRoleMenu | null> {
    const sql = 'SELECT * FROM sys_role_menu WHERE id = ? AND disabled = 0';
    const [rows] = await this.pool.execute(sql, [id]);
    const result = rows as SysRoleMenu[];
    return result.length > 0 ? result[0] : null;
  }

  async list(
    page: number = 1,
    pageSize: number = 10,
    conditions: SysRoleMenuQueryDto = {}
  ): Promise<{
    list: SysRoleMenu[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    const whereConditions: string[] = ['disabled = 0'];
    const params: (string | number)[] = [];

    if (conditions.role_id !== undefined) {
      whereConditions.push('role_id = ?');
      params.push(conditions.role_id);
    }
    if (conditions.menu_id !== undefined) {
      whereConditions.push('menu_id = ?');
      params.push(conditions.menu_id);
    }
    if (conditions.is_half !== undefined) {
      whereConditions.push('is_half = ?');
      params.push(conditions.is_half);
    }

    const whereClause = whereConditions.join(' AND ');
    const countSql = `SELECT COUNT(*) as total FROM sys_role_menu WHERE ${whereClause}`;
    const [countResult] = await this.pool.execute(countSql, params);
    const total = (countResult as { total: number }[])[0].total;

    const offset = (page - 1) * pageSize;
    const listSql = `SELECT * FROM sys_role_menu WHERE ${whereClause} ORDER BY id DESC LIMIT ? OFFSET ?`;
    const [rows] = await this.pool.execute(listSql, [...params, pageSize, offset]);

    return {
      list: rows as SysRoleMenu[],
      total,
      page,
      pageSize,
    };
  }
}

// PostgreSQL数据库操作实现
class PostgreSQLOperations
  extends BasePostgreSQLOperations<SysRoleMenu>
  implements SysRoleMenuOperations
{
  constructor(pool: PgPool) {
    super(pool);
  }

  async create(roleMenu: CreateSysRoleMenuDto): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_role_menu (
                      role_id, menu_id, is_half, time, update_time
                  ) VALUES ($1, $2, $3, $4, $5) RETURNING id`;
    const params = [roleMenu.role_id, roleMenu.menu_id, roleMenu.is_half, now, now];

    const result = await this.pool.query(sql, params);
    return result.rows[0].id;
  }

  async update(id: number, roleMenu: UpdateSysRoleMenuDto): Promise<boolean> {
    const updateFields: string[] = [];
    const params: (string | number)[] = [];
    let paramIndex = 1;

    Object.entries(roleMenu).forEach(([key, value]) => {
      if (value !== undefined && key !== 'id' && key !== 'time') {
        updateFields.push(`${key} = $${paramIndex}`);
        params.push(value as string | number);
        paramIndex++;
      }
    });

    if (updateFields.length === 0) {
      return false;
    }

    updateFields.push(`update_time = $${paramIndex}`);
    params.push(Date.now());
    paramIndex++;

    params.push(id);
    const sql = `UPDATE sys_role_menu SET ${updateFields.join(', ')} WHERE id = $${paramIndex} AND disabled = 0`;
    const result = await this.pool.query(sql, params);
    return result.rowCount !== null && result.rowCount > 0;
  }

  async delete(id: number): Promise<boolean> {
    const sql = 'UPDATE sys_role_menu SET disabled = 1, update_time = $1 WHERE id = $2';
    const result = await this.pool.query(sql, [Date.now(), id]);
    return result.rowCount !== null && result.rowCount > 0;
  }

  async getByRoleId(roleId: number): Promise<SysRoleMenu[]> {
    const sql = 'SELECT * FROM sys_role_menu WHERE role_id = $1 AND disabled = 0';
    const result = await this.pool.query(sql, [roleId]);
    return result.rows;
  }

  async getByMenuId(menuId: number): Promise<SysRoleMenu[]> {
    const sql = 'SELECT * FROM sys_role_menu WHERE menu_id = $1 AND disabled = 0';
    const result = await this.pool.query(sql, [menuId]);
    return result.rows;
  }

  async getByRoleAndMenu(roleId: number, menuId: number): Promise<SysRoleMenu | null> {
    const sql = 'SELECT * FROM sys_role_menu WHERE role_id = $1 AND menu_id = $2 AND disabled = 0';
    const result = await this.pool.query(sql, [roleId, menuId]);
    return result.rows.length > 0 ? result.rows[0] : null;
  }

  async deleteByRoleId(roleId: number): Promise<boolean> {
    const sql = 'UPDATE sys_role_menu SET disabled = 1, update_time = $1 WHERE role_id = $2';
    const result = await this.pool.query(sql, [Date.now(), roleId]);
    return result.rowCount !== null && result.rowCount > 0;
  }

  async batchCreate(roleMenus: CreateSysRoleMenuDto[]): Promise<boolean> {
    if (roleMenus.length === 0) return true;

    const client = await this.pool.connect();
    try {
      await client.query('BEGIN');

      const now = Date.now();
      for (const roleMenu of roleMenus) {
        const sql = `INSERT INTO sys_role_menu (role_id, menu_id, is_half, time, update_time) 
                     VALUES ($1, $2, $3, $4, $5)`;
        await client.query(sql, [roleMenu.role_id, roleMenu.menu_id, roleMenu.is_half, now, now]);
      }

      await client.query('COMMIT');
      return true;
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }

  async getRoleMenuTree(roleId: number): Promise<RoleMenuTreeDto> {
    const sql = 'SELECT menu_id, is_half FROM sys_role_menu WHERE role_id = $1 AND disabled = 0';
    const result = await this.pool.query(sql, [roleId]);
    const roleMenus = result.rows as { menu_id: number; is_half: number }[];

    const checkedMenuIds: number[] = [];
    const halfCheckedMenuIds: number[] = [];

    roleMenus.forEach(rm => {
      if (rm.is_half === 0) {
        checkedMenuIds.push(rm.menu_id);
      } else {
        halfCheckedMenuIds.push(rm.menu_id);
      }
    });

    return {
      role_id: roleId,
      checked_menu_ids: checkedMenuIds,
      half_checked_menu_ids: halfCheckedMenuIds,
    };
  }

  async setRoleMenuPermissions(data: RoleMenuPermissionDto): Promise<boolean> {
    const client = await this.pool.connect();
    try {
      await client.query('BEGIN');

      // 删除该角色的所有菜单权限
      await client.query(
        'UPDATE sys_role_menu SET disabled = 1, update_time = $1 WHERE role_id = $2',
        [Date.now(), data.role_id]
      );

      // 批量插入新的菜单权限
      const now = Date.now();

      // 添加选中的菜单
      for (const menuId of data.menu_ids) {
        const sql = `INSERT INTO sys_role_menu (role_id, menu_id, is_half, time, update_time) 
                     VALUES ($1, $2, $3, $4, $5)`;
        await client.query(sql, [data.role_id, menuId, 0, now, now]);
      }

      // 添加半选的菜单
      if (data.half_menu_ids && data.half_menu_ids.length > 0) {
        for (const menuId of data.half_menu_ids) {
          const sql = `INSERT INTO sys_role_menu (role_id, menu_id, is_half, time, update_time) 
                       VALUES ($1, $2, $3, $4, $5)`;
          await client.query(sql, [data.role_id, menuId, 1, now, now]);
        }
      }

      await client.query('COMMIT');
      return true;
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }

  async getById(id: number): Promise<SysRoleMenu | null> {
    const sql = 'SELECT * FROM sys_role_menu WHERE id = $1 AND disabled = 0';
    const result = await this.pool.query(sql, [id]);
    return result.rows.length > 0 ? result.rows[0] : null;
  }

  async list(
    page: number = 1,
    pageSize: number = 10,
    conditions: SysRoleMenuQueryDto = {}
  ): Promise<{
    list: SysRoleMenu[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    const whereConditions: string[] = ['disabled = 0'];
    const params: (string | number)[] = [];
    let paramIndex = 1;

    if (conditions.role_id !== undefined) {
      whereConditions.push(`role_id = $${paramIndex}`);
      params.push(conditions.role_id);
      paramIndex++;
    }
    if (conditions.menu_id !== undefined) {
      whereConditions.push(`menu_id = $${paramIndex}`);
      params.push(conditions.menu_id);
      paramIndex++;
    }
    if (conditions.is_half !== undefined) {
      whereConditions.push(`is_half = $${paramIndex}`);
      params.push(conditions.is_half);
      paramIndex++;
    }

    const whereClause = whereConditions.join(' AND ');
    const countSql = `SELECT COUNT(*) as total FROM sys_role_menu WHERE ${whereClause}`;
    const countResult = await this.pool.query(countSql, params);
    const total = parseInt(countResult.rows[0].total);

    const offset = (page - 1) * pageSize;
    const listSql = `SELECT * FROM sys_role_menu WHERE ${whereClause} ORDER BY id DESC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
    const listResult = await this.pool.query(listSql, [...params, pageSize, offset]);

    return {
      list: listResult.rows,
      total,
      page,
      pageSize,
    };
  }
}

export class SysRoleMenuService {
  private dbOperations: SysRoleMenuOperations;

  constructor() {
    this.dbOperations = DatabaseOperationsFactory.create<SysRoleMenuOperations>(
      MySQLOperations,
      PostgreSQLOperations
    );
  }

  async create(roleMenu: CreateSysRoleMenuDto): Promise<number> {
    return await this.dbOperations.create(roleMenu);
  }

  async update(id: number, roleMenu: UpdateSysRoleMenuDto): Promise<boolean> {
    return await this.dbOperations.update(id, roleMenu);
  }

  async delete(id: number): Promise<boolean> {
    const existing = await this.dbOperations.getById(id);
    if (!existing) {
      throw new BusinessException('角色菜单关联不存在', 404);
    }
    return await this.dbOperations.delete(id);
  }

  async getByRoleId(roleId: number): Promise<SysRoleMenu[]> {
    return await this.dbOperations.getByRoleId(roleId);
  }

  async getByMenuId(menuId: number): Promise<SysRoleMenu[]> {
    return await this.dbOperations.getByMenuId(menuId);
  }

  async getByRoleAndMenu(roleId: number, menuId: number): Promise<SysRoleMenu | null> {
    return await this.dbOperations.getByRoleAndMenu(roleId, menuId);
  }

  async deleteByRoleId(roleId: number): Promise<boolean> {
    return await this.dbOperations.deleteByRoleId(roleId);
  }

  async batchCreate(roleMenus: CreateSysRoleMenuDto[]): Promise<boolean> {
    return await this.dbOperations.batchCreate(roleMenus);
  }

  async getRoleMenuTree(roleId: number): Promise<RoleMenuTreeDto> {
    return await this.dbOperations.getRoleMenuTree(roleId);
  }

  async setRoleMenuPermissions(data: RoleMenuPermissionDto): Promise<boolean> {
    return await this.dbOperations.setRoleMenuPermissions(data);
  }

  async getById(id: number): Promise<SysRoleMenu | null> {
    return await this.dbOperations.getById(id);
  }

  async list(
    page: number = 1,
    pageSize: number = 10,
    query: SysRoleMenuQueryDto = {}
  ): Promise<{ total: number; list: SysRoleMenu[]; page: number; pageSize: number }> {
    return await this.dbOperations.list(page, pageSize, query);
  }
}
